﻿using HCQ2_Common;
using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VR;
using HCQ2_Model.VRDD.InputDto._20220625;
using HCQ2_Model.VRDD.InputDto._20220704;
using HCQ2_Model.VRDD.InputDto._211217;
using HCQ2_Model.VRDD.OutputDto._20220625;
using HCQ2_Model.VRDD.OutputDto._20220704;
using HCQ2_Model.VRDD.OutputDto._211217;
using HCQ2_Model.VRQDMeta.InputDto._20220418;
using HCQ2_Model.VRQDMeta.OutputDto._20220418;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRStayUserLogDAL: IT_VRStayUserLogDAL
    {
        /// <summary>
        ///  获取楼宇入住日志
        /// </summary>
        /// <param name="file_id"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public List<OutputBuildLog> GetBuildLogList(int file_id, int page, int rows,out int total)
        {
            sb?.Clear();
            sb.Append($@"(select user_code,CONVERT(varchar(100),in_date,120) AS in_date,CONVERT(varchar(100),out_date,120) 
            AS out_date,(ROW_NUMBER() OVER(ORDER BY in_date desc))AS rowNum from T_VRStayUserLog where file_id={file_id}) l inner join
            (select user_id,user_guid,user_name,user_img,user_phone from T_User)u on l.user_code=u.user_guid ");
            total= SqlHelper.ExecuteScalarInt32($"select count(0) from {sb}");
            return SqlHelper.ExecuteList<OutputBuildLog>($"select top {rows} * from {sb} where l.rowNum>{(page - 1) * rows}");
        }
        /// <summary>
        ///  添加入住日志返回数据
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public OutputSaveHouse GetBuildData(InputSaveHouse param)
        {
            sb?.Clear();
            sb.Append($@"select info.*,folder.folder_id as folderID,folder.folder_path as folderPath,folder.folder_name as folderName from 
            (select top 1 file_id as fileID,i.file_note AS fileNote,i.programId AS programID,file_name as filename,file_code as fileCode,fileImage='',doc_number as docNumber,i.update_time,
            file_order as fileOrder,filePrice=(SELECT MIN(ori_price) FROM dbo.T_VRGoodDisiPrice 
            WHERE file_id=i.file_id) from T_DocumentInfo i where ");
            if (!string.IsNullOrEmpty(param.docNumber))
                sb.Append($" doc_number='{param.docNumber}' ");
            else
                sb.Append($" ISNULL(doc_number,'')='' ");
            //判断fileCode是否存在
            if (!string.IsNullOrEmpty(param.fileCode))
                sb.Append($@" AND i.file_code='{param.fileCode}' )info INNER join (select file_id,folder_id from T_DocumentFolderRelation)rel 
            on info.fileID=rel.file_id inner join(select folder_id,folder_path,folder_name from T_DocumentFolder)folder on rel.folder_id=folder.folder_id
			ORDER BY info.update_time DESC;");
            else
                sb.Append($@" and exists(select file_id from T_DocumentFolderRelation r where folder_path like '{param.folderPath}%' and i.file_id=r.file_id)
            order by file_order asc)info inner join
            (select file_id,folder_id from T_DocumentFolderRelation where folder_path like '{param.folderPath}%')rel on info.fileID=rel.file_id
            inner join(select folder_id,folder_path,folder_name from T_DocumentFolder)folder on rel.folder_id=folder.folder_id ORDER BY info.update_time DESC;");
            return SqlHelper.ExecuteList<OutputSaveHouse>(sb.ToString())?.FirstOrDefault();
        }

        /// <summary>
        ///  获取楼层明细
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<FloorDetial> GetFloorDetialList(InputHouseInfo param)
        {
            sb?.Clear();
            sb.Append($@"SELECT f.doc_number AS floorList,
            available=(CASE WHEN (SELECT COUNT(0) FROM dbo.T_DocumentInfo WHERE doc_number=f.doc_number 
            AND ISNULL(qr_code,'')='')>0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END) FROM 
            (SELECT i.doc_number FROM dbo.T_DocumentInfo i WHERE i.file_status=5 AND i.if_remove=0 
            AND page_type='{param.docType}' AND EXISTS(SELECT file_id FROM dbo.T_DocumentFolderRelation r 
            WHERE r.folder_path LIKE '{param.folderPath}%' AND r.file_id=i.file_id)GROUP BY i.doc_number)f;");
            return SqlHelper.ExecuteList<FloorDetial>(sb.ToString());
        }
        /// <summary>
        ///  获取商品明细
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<FileDetial> GetFileDetialList(InputHouseInfo param,int user_id)
        {
            sb?.Clear();
            sb.Append($@"SELECT info.*,filePrice=(SELECT MIN(ori_price) FROM dbo.T_VRGoodDisiPrice 
            WHERE file_id=info.file_id),fileImage=(SELECT TOP 1 file_url FROM dbo.T_DocBagFileRecord WHERE 
            file_id=info.file_id AND file_type=0),rel.folderID,rel.folderPath,rel.folderName,u.*,d.* FROM 
            (SELECT file_id,folder_id AS folderID,folder_path AS folderPath,
            folderName=(SELECT folder_name FROM dbo.T_DocumentFolder f WHERE f.folder_id=r.folder_id)
            FROM dbo.T_DocumentFolderRelation r WHERE folder_path LIKE '{param.folderPath}%')rel INNER JOIN
            (SELECT file_id,file_name AS filename,file_code AS fileCode,alias_name AS aliasName,programId AS programID,
            doc_number AS docNumber,file_order AS fileOrder,qr_code,issue_name FROM dbo.T_DocumentInfo WHERE file_status=5 
            AND if_remove=0 AND page_type='{param.docType}')info ON info.file_id = rel.file_id LEFT JOIN
            (SELECT user_id AS ownerUserID,user_name AS ownerUserName,user_img AS ownerUserImage,user_guid AS 
            userid FROM dbo.T_User)u ON u.userid=info.issue_name LEFT JOIN (SELECT du_name AS ownerRealName,du_id,hide_name,
            friend=(SELECT COUNT(0) FROM dbo.DD_FriendApply WHERE ((du_id={user_id} AND apply_id=dp.du_id) OR 
            (apply_id={user_id} OR du_id=dp.du_id)) AND apply_status=1) 
            FROM dbo.DD_User dp)d ON u.ownerUserID=d.du_id;");
            return SqlHelper.ExecuteList<FileDetial>(sb.ToString());
        }
        /// <summary>
        ///  获取商品图片明细
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<ImageDetial> GetImageDetial(InputHouseInfo param)
        {
            sb?.Clear();
            sb.Append($@"SELECT r.* FROM 
            (SELECT file_id FROM dbo.T_DocumentFolderRelation r WHERE folder_path LIKE '{param.folderPath}%')rel INNER JOIN
            (SELECT file_id FROM dbo.T_DocumentInfo WHERE file_status=5 
            AND if_remove=0 AND page_type='{param.docType}')info ON info.file_id = rel.file_id INNER JOIN
            (SELECT bag_id AS imageID,file_url AS imageUrl,file_type AS fileType,file_id FROM dbo.T_DocBagFileRecord)r
            ON r.file_id = info.file_id ORDER BY r.fileType DESC,r.imageID DESC;");
            return SqlHelper.ExecuteList<ImageDetial>(sb.ToString());
        }
        /// <summary>
        ///  统计已入住用户数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetHouseSum(InputHouseInfo param)
        {
            sb?.Clear();
            sb.Append($@"SELECT COUNT(0)AS num FROM dbo.T_DocumentInfo i WHERE i.file_status=5 AND i.if_remove=0 
            AND page_type='{param.docType}' AND EXISTS(SELECT file_id FROM dbo.T_DocumentFolderRelation r 
            WHERE r.folder_path LIKE '{param.folderPath}%' AND r.file_id=i.file_id) AND ISNULL(i.issue_name,'')<>'';");
            return Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取入住用户所属目录及全部父目录
        /// </summary>
        /// <param name="folder_id"></param>
        /// <returns></returns>
        public List<UserFolderDetial> GetUserFloderInfo(int folder_id)
        {
            sb?.Clear();
            sb.Append($@"WITH temp AS(
            SELECT folder_id AS folderID,folder_name AS folderName,folder_pid,folder_path AS folderPath,folder_note AS 
            folderNote,folder_url AS folderImage,group_name AS groupName,
            city,addr AS bsAddress,gps_x,gps_y,1 AS[level],bs_type AS bsType FROM dbo.T_DocumentFolder WHERE folder_id={folder_id}
            UNION all
            SELECT a.folder_id AS folderID,a.folder_name AS folderName,a.folder_pid,a.folder_path AS folderPath,a.folder_note 
            AS folderNote,a.folder_url AS folderImage,a.group_name AS groupName,
            a.city,a.addr AS bsAddress,a.gps_x,a.gps_y,temp.[level]+1,bs_type AS bsType FROM dbo.T_DocumentFolder a
            INNER JOIN temp ON a.folder_id = temp.folder_pid)
            SELECT *,CAST(ROW_NUMBER() OVER(ORDER BY temp.level DESC)AS INT)AS folderLevel FROM temp;");
            return SqlHelper.ExecuteList<UserFolderDetial>(sb.ToString());
        }
        /// <summary>
        ///  获取入住用户所属目录及全部父目录
        /// </summary>
        /// <param name="folder_id"></param>
        /// <returns></returns>
        public List<ParentFolder> GetFloderAndParent(int folder_id)
        {
            sb?.Clear();
            sb.Append($@"WITH temp AS(
            SELECT folder_id AS folderID,folder_name AS folderName,folder_pid,folder_path AS folderPath,folder_note AS 
            folderNote,group_name AS groupName,
            folderImage=(SELECT file_url FROM dbo.T_DocBagFolderRecord WHERE folder_id=f.folder_id AND folder_type=0),
            addr AS bsAddress,1 AS[level],bs_type AS bsType FROM dbo.T_DocumentFolder f WHERE folder_id={folder_id}
            UNION all
            SELECT a.folder_id AS folderID,a.folder_name AS folderName,a.folder_pid,a.folder_path AS folderPath,a.folder_note 
            AS folderNote,a.group_name AS groupName,
            folderImage=(SELECT file_url FROM dbo.T_DocBagFolderRecord WHERE folder_id=a.folder_id AND folder_type=0),
            a.addr AS bsAddress,temp.[level]+1,bs_type AS bsType FROM dbo.T_DocumentFolder a
            INNER JOIN temp ON a.folder_id = temp.folder_pid)
            SELECT *,CAST(ROW_NUMBER() OVER(ORDER BY temp.level DESC)AS INT)AS folderLevel FROM temp;");
            return SqlHelper.ExecuteList<ParentFolder>(sb.ToString());
        }
        /// <summary>
        ///  获取入住人用户所住商品信息
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public OutputUserHouseInfo GetUserHouseInfo(BaseVRAPI param)
        {
            sb?.Clear();
            sb.Append($@"SELECT info.*,rel.folderID,rel.folderPath,folderName=(SELECT folder_name FROM dbo.T_DocumentFolder 
            WHERE folder_id=rel.folderID) FROM (SELECT file_id,file_name AS filename,file_code AS fileCode,
            fileImage=(SELECT TOP 1 file_url FROM dbo.T_DocBagFileRecord WHERE file_id=i.file_id AND file_type=0),
            doc_number AS docNumber,file_order AS fileOrder,filePrice=(SELECT MIN(ori_price) FROM dbo.T_VRGoodDisiPrice 
            WHERE file_id=i.file_id) FROM dbo.T_DocumentInfo i WHERE 1=1)info INNER JOIN
            (SELECT file_id,folder_id AS folderID,folder_path AS folderPath FROM 
            dbo.T_DocumentFolderRelation)rel ON rel.file_id = info.file_id INNER JOIN
            (SELECT g.file_id FROM 
	        (SELECT file_id,ROW_NUMBER() OVER(PARTITION BY user_code,file_code ORDER BY in_date DESC)AS num 
	        FROM T_VRStayUserLog WHERE user_code='{param.userID}' AND out_date IS NULL)g WHERE g.num=1)l ON info.file_id=l.file_id;");
            return SqlHelper.ExecuteList<OutputUserHouseInfo>(sb.ToString())?.FirstOrDefault();
        }
        /// <summary>
        ///  获取用户住宅列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputUserHouse> GetUserHouseList(InputUserHouse param,out int total)
        {
            sb?.Clear();
            sb.Append($@"(SELECT info.vUrl as selfImage,coverImage,logs.*,aliasName=(CASE WHEN logs.user_code<>info.issue_name THEN '' 
            ELSE info.aliasName END),info.fileID,info.fileName,info.fileNote,info.fileImage,info.folderId,
            info.fileCode,info.programID,ROW_NUMBER() OVER(ORDER BY logs.stayTime DESC) AS num FROM 
            (SELECT file_id,user_code,CONVERT(varchar(100),in_date,120) AS stayTime,
            CONVERT(varchar(100),out_date,120) AS outTime,stayType,
            CAST(stayDefault AS BIT)AS isDefault FROM dbo.T_VRStayUserLog WHERE user_code='{param.userid}' ");
            //入住类别
            if (param.stayType > -1)
                sb.Append($" AND stayType={param.stayType} ");
            //默认住宅
            if (param.@default > -1)
                sb.Append($" AND stayDefault={param.@default } ");
            //类别
            if (param.type ==0)
                //已搬出
                sb.Append($" AND out_date IS NOT NULL ");
            else if (param.type == 1)
                //居住中
                sb.Append($" AND out_date IS NULL ");
            sb.Append($@")logs INNER JOIN 
            (SELECT file_id AS fileID,file_name AS fileName,file_note AS fileNote,i.vUrl,
            fileImage=(SELECT TOP 1 file_url FROM dbo.T_DocBagFileRecord WHERE file_id=i.file_id AND file_type=0),
            coverImage=(SELECT TOP 1 file_url FROM dbo.T_DocBagFileRecord WHERE file_id=i.file_id AND file_type=1),
            folderId=(SELECT TOP 1 folder_id FROM dbo.T_DocumentFolderRelation WHERE file_id=i.file_id),
            alias_name AS aliasName,issue_name,file_code AS fileCode,programId AS 
            programID FROM dbo.T_DocumentInfo i)info ON logs.file_id=info.fileID ");
            if (!string.IsNullOrEmpty(param.folderPath))
                sb.Append($@" INNER JOIN (SELECT file_id FROM dbo.T_DocumentFolderRelation 
            WHERE folder_path LIKE '{param.folderPath}%')rel ON info.fileID=rel.file_id ");
            sb.Append($")g ");
            total = SqlHelper.ExecuteScalarInt32($" SELECT COUNT(0) FROM {sb.ToString()} ");
            return SqlHelper.ExecuteList<OutputUserHouse>($" SELECT TOP {param.rows} * FROM {sb.ToString()} WHERE g.num>{(param.page - 1) * param.rows}");
        }
        /// <summary>
        /// 获取用户住宅详情
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public OutputGetUserHouseDetail GetUserHouseDetail(InputGetUserHouseDetail param)
        {
            sb.Clear();
            sb.Append($@"select l.file_id as fileID,i.file_name as fileName,i.file_note as fileNote,i.alias_name as aliasName,i.file_code as fileCode,
                i.vUrl as selfImage,isDefault=(cast (l.stayDefault as bit)),i.programId as programID , l.stayType as stayType,
                CONVERT(varchar(100),l.in_date,120) as stayTime,CONVERT(varchar(100),l.out_date,120) as outTime,
                fileImage=(select top 1 file_url from T_DocBagFileRecord where file_id={param.fileID} and file_type=0) ,
                coverImage =(select top 1 file_url from T_DocBagFileRecord where file_id={param.fileID} and file_type=1) ,
                folderId =(select folder_id from T_DocumentFolderRelation where file_id= {param.fileID})
                from T_VRStayUserLog l join T_DocumentInfo i on l.file_id=i.file_id
                and l.user_code='{param.userid}' and l.file_id={param.fileID} and l.out_date is null");
            return SqlHelper.ExecuteList<OutputGetUserHouseDetail>(sb.ToString())?.FirstOrDefault();
        }
        /// <summary>
        /// 获取住宅详情
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public OutputGetHouseDetail GetHouseDetail(InputGetHouseDetail param)
        {
            sb.Clear();
            sb.Append($@"select l.file_id as fileID,l.file_name as fileName,i.file_note as fileNote,i.alias_name as aliasName,l.file_code as fileCode,
            i.vUrl as selfImage,l.user_code as ownerUserID,u.user_id as ownerID,u.user_phone as ownerUserPhone,
            u.user_name as ownerUserName,u.user_img as ownerUserImage,
            --isDefault=(cast (l.stayDefault as bit)),
            i.programId as programID , 
            --l.stayType as stayType,
            --CONVERT(varchar(100),l.in_date,120) as stayTime,CONVERT(varchar(100),l.out_date,120) as outTime,
            filePrice=(select top 1 ori_price from T_VRGoodDisiPrice where file_id=l.file_id order by ori_price asc ),
            fileImage=(select top 1 file_url from T_DocBagFileRecord where file_id=l.file_id and file_type=0) ,
            coverImage =(select top 1 file_url from T_DocBagFileRecord where file_id=l.file_id and file_type=1) ,
            folderId =(select folder_id from T_DocumentFolderRelation where file_id= l.file_id),
            folderPath =(select folder_path from T_DocumentFolderRelation where file_id= l.file_id),
            folderName =(select folder_name from T_DocumentFolder where folder_id=( select folder_id from T_DocumentFolderRelation where file_id= l.file_id))
            from T_VRStayUserLog l join T_DocumentInfo i on l.file_id=i.file_id join T_User u on l.user_code=u.user_guid
            and l.file_id={param.fileID} and l.out_date is null");
            return SqlHelper.ExecuteList<OutputGetHouseDetail>(sb.ToString())?.FirstOrDefault();
        }
        /// <summary>
        /// 返回入住人列表
        /// </summary>
        /// <param name="fileID"></param>
        /// <returns></returns>
        public List<stays> stayList(int fileID)
        {
            sb.Clear();
            sb.Append($@"select CONVERT(varchar(100),in_date,120) as stayTime,
            user_code as userid,
            user_id as id,
            user_name as userName,
            user_phone as userPhone,
            user_img as userImage,
            stayType
            from T_VRStayUserLog sul join T_User u on sul.user_code=u.user_guid where file_id =2999 
            and out_date is null order by stayType desc ,in_date desc");
            return SqlHelper.ExecuteList<stays>(sb.ToString());
        }

    }
}